package com.auxgroup.hr.front.dao.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.auxgroup.hr.front.dao.domain.PsCWfPlaveHdr;

import java.util.Date;
import java.util.List;

/**
 * @author lv-PC
 */
@Repository
public interface PsCWfPlaveHdrRepository extends
        JpaRepository<PsCWfPlaveHdr, String>, JpaSpecificationExecutor<PsCWfPlaveHdr> {

    @Modifying
    @Transactional
    @Query(value = "delete from PsCWfPlaveHdr where cTransactionNbr=:transNum")
    Integer deleteByTransNum(@Param("transNum") String transNum);

    /**
     * 获取产检天数总计
     * @param cWfStatus 流程状态
     * @param cPregnantTime 怀孕时长
     * @param startDate 开始时间
     * @param emplid 员工工号
     * @return
     */
    @Query(value = "SELECT SUM(C_LEAVE_DAYS)  FROM PS_C_WF_PLAVE_HDR WHERE C_WF_STATUS=:cWfStatus AND C_LEAVE_TYPE= 'L200' AND C_PREGNANT_TIME=:cPregnantTime AND to_char(START_DATE,'yyyy-MM')=to_char(:startDate,'yyyy-MM') AND C_WF_EMPLID=:emplid", nativeQuery = true)
    List countApplyingPregnantDays(@Param("cWfStatus") String cWfStatus, @Param("cPregnantTime") String cPregnantTime, @Param("startDate") Date startDate, @Param("emplid") String emplid);
}